package o2o.web.actions.statis;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jinyou.utils.common.*;
import jxl.Workbook;
import jxl.write.Number;
import jxl.write.*;
import o2o.cache.shop.ShopMemory;
import o2o.cache.user.UserInfoMemory;
import o2o.cache.user.UserOnlineImpl;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.common.UserType;
import o2o.dao.PO.order.ORDER_STATUS;
import o2o.dao.PO.shop.CLOUD_SHOP_TYPE;
import o2o.company.model.ShopInfo;
import o2o.dao.PO.user.UserInfo;
import o2o.dao.VO.order.REFUND_APPLY_STATUS;
import o2o.dao.VO.statis.ShopAndCityVo;
import o2o.dao.VO.statis.ShopGoodsSell;
import o2o.dao.VO.statis.TradeStatisByDayInfo;
import o2o.system.data.SYS_CONFIG_PATH;
import o2o.system.data.SYS_SETTINGS;
import o2o.task.order.OrderBakTaskUtils;
import o2o.web.actions.company.SysCityCompanyMan;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.File;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.*;

import static com.jinyou.utils.common.AjaxResult.successResult;

/**
 * @Description: TODO
 * Created with IntelliJ IDEA.
 * User: Jahnke [1029777564@qq.com]
 * Date: 2019-02-19 14:48
 * To change this template use File | Settings | File Templates.
 */
@Controller
public class CityAgentStatisAdminController {

    // 城市营业统计(统计城市下面的所有城代管理的订单营业信息)  2019年2月19日 12:31:48  Jahnke
    @RequestMapping(method = RequestMethod.POST, value = "admin/money/city/statistics")
    @ResponseBody
    public String cityAgentStatis01(String token, String province, Integer isExcel, String county, String city, Integer page, Integer size, Long startTime, Long endTime, String username, Long pid) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代可查看

        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.COMPANY && user.getUserType() != UserType.SUPER_COMPANY && user.getUserType() != UserType.SUPER_ADMIN) {
            return successResult(null, 0);
        }
        if (user.getUserType() == UserType.COMPANY) {
            user.setUserType(UserType.ADMIN);
            String t = SysCityCompanyMan.getSysCompanyOwnUsername(user.getUsername());
            if (StringUtils.isEmpty(t)) {
                return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
            }
            user.setUsername(t);
        }

        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }

        if (endTime == null) {
            endTime = new Date().getTime();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }
        /*
        *  SELECT a.*,
                userinfo.name ,
                SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.totalprice,0) ELSE 0 END) 支付金额,
                SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.totalmoney,0) ELSE 0 END) 商品总费用,
                SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN 1 ELSE 0 END) orderCounts,SUM(CASE WHEN ori.isRefundApply=2 THEN 1 ELSE 0 END) tuicount,SUM(CASE WHEN ori.isRefundApply=2  THEN ori.totalprice ELSE 0 END) 退款金额,
                SUM(IFNULL(orp.cityAdminMoney,0)) AS 城代金额, SUM(IFNULL(orp.shopAwardMoney,0)) AS 店铺补贴,SUM(IFNULL(orp.platformAwardMoney,0)) AS 平台补贴,SUM(IFNULL(orp.shopMoney,0)) AS 商家金额,SUM(IFNULL(orp.postmanMoney,0)) AS 配送费
            FROM (SELECT DISTINCT uagre.agentId AS userAgentId,uagre.username AS agentUsername
                FROM user_agent_relation AS uagre
                LEFT JOIN city_agent AS cag ON uagre.agentId=cag.id
                WHERE cag.province='河北' AND cag.county='大城县')
            AS a
            LEFT JOIN user_info AS userinfo ON userinfo.username=a.agentUsername and a.agentUsername='10001000801'
            LEFT JOIN order_info AS ori ON ori.agentId=a.userAgentId AND ori.delFlag=0 AND ori.createtime BETWEEN 1522719130713 AND 1522719130713
            LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno
            GROUP BY a.agentUsername
        * */
//        Set<Long> cityAdminIdByPId = StatisCityAdminMan.listOfCityAdminIdByPId(pid, true);
//        String cityId = "";
//        if (cityAdminIdByPId != null && cityAdminIdByPId.size() > 0) {
//            for (Long cityAdminId : cityAdminIdByPId) {
//                cityId += cityAdminId + ",";
//            }
//        }


//        String sql = "SELECT a.*," +
//                "   userinfo.name , uwt.totalMoney, uwt.money,uwt.popMoney,uwt.popTotalMoney,orp.cityAdmin,orp.cityAdmin1,orp.cityAdmin2,orp.cityAdmin3,orp.cityAdmin4," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.totalprice,0) ELSE 0 END) allPayMoney," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.totalmoney,0) ELSE 0 END) allPayPrice," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.totalGoodsMoney,0) ELSE 0 END) totalGoodsMoney," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.packetPrice,0) ELSE 0 END) packetPrice," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN 1 ELSE 0 END) orderCounts," +
//                "   SUM(CASE WHEN ori.isRefundApply=2 or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN 1 ELSE 0 END) refundApplyCount," +
//                "   SUM(CASE WHEN ori.isRefundApply=2 or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN ori.totalprice ELSE 0 END) refundApplyMoney," +
//
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(orp.shopAwardMoney,0) ELSE 0 END) AS shopAwardMoney," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(orp.platformAwardMoney,0) ELSE 0 END) AS platformAwardMoney," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(orp.platformMoney,0) ELSE 0 END) AS platformMoney," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(orp.shopMoney,0) ELSE 0 END) AS shopMoney," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(orp.postmanMoney,0) ELSE 0 END) AS postmanMoney, " +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.originalYunFeiShop,0) ELSE 0 END) AS originalYunFeiShop," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.originalYunFei,0) ELSE 0 END) AS originalYunFei," +
//                "   SUM(CASE WHEN ori.orderstatus=9 AND ori.isRefundApply!=2 THEN IFNULL(ori.deliveryPrice,0) ELSE 0 END) AS deliveryPrice, " +
//                "   IFNULL((SELECT SUM(cityAdminMoney) cityAgentMoney FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin), 0) cityAgentMoney, " +
//                "   IFNULL((SELECT SUM(cityAdminAward) cityAdminAward FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin), 0) cityAdminAward, " +
//                "   IFNULL((SELECT SUM(cityAdminMoney1) cityAdminMoney1 FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin1 AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin1), 0) cityAdminMoney1, " +
//                "   IFNULL((SELECT SUM(cityAdminMoney2) cityAdminMoney2 FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin2 AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin2), 0) cityAdminMoney2, " +
//                "   IFNULL((SELECT SUM(cityAdminMoney3) cityAdminMoney3 FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin3 AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin3), 0) cityAdminMoney3, " +
//                "   IFNULL((SELECT SUM(cityAdminMoney4) cityAdminMoney4 FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin4 AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin4), 0) cityAdminMoney4, " +
//                "   IFNULL((SELECT SUM(cityAdminAward1) cityAdminAward1 FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin1 AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin1), 0) cityAdminAward1, " +
//                "   IFNULL((SELECT SUM(cityAdminAward2) cityAdminAward2 FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin2 AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin2), 0) cityAdminAward2, " +
//                "   IFNULL((SELECT SUM(cityAdminAward3) cityAdminAward3 FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin3 AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin3), 0) cityAdminAward3, " +
//                "   IFNULL((SELECT SUM(cityAdminAward4) cityAdminAward4 FROM order_pay orp WHERE isordercancel = 0 AND a.agentUsername = orp.cityAdmin4 AND orp.createTime BETWEEN " + startTime + " AND " + endTime + " GROUP BY orp.cityAdmin4), 0) cityAdminAward4," +
//                "   IFNULL(uwt.money,0) money," +
//                "   IFNULL(uwt.totalMoney,0) totalMoney," +
//                "   IFNULL(uwt.popTotalMoney,0) popTotalMoney," +
//                "   IFNULL(uwt.popMoney,0) popMoney" +
////                "   FROM (SELECT DISTINCT uagre.agentId AS userAgentId,uagre.username AS agentUsername,cag.province AS agentProv,cag.city AS agentCity,cag.county AS agentCounty" +// 应该是城代所在省市区，不是商圈省市区
//                "   FROM (SELECT DISTINCT uagre.agentId AS userAgentId,uagre.username AS agentUsername,sca.province AS agentProv,sca.city AS agentCity,sca.county AS agentCounty" +
//                "   FROM " + PojoTables.USER_AGENT_RELATION + " AS uagre" +
//                "   LEFT JOIN " + PojoTables.SysCityAdmin + " sca ON sca.username=uagre.username" +
//                "   LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON uagre.agentId=cag.id" +
//                "   WHERE cag.delFlag=0 AND uagre.delFlag = 0  " +
//                "       " + (StringUtils.isNotEmpty(province) ? " and sca.province='" + province + "'" : "") +
//                "       " + (StringUtils.isNotEmpty(city) ? " and sca.city='" + city + "'" : "") +
//                "       " + (StringUtils.isNotEmpty(county) ? " and sca.county='" + county + "'" : "") +
//                "       " + (StringUtils.isNotEmpty(username) ? " and uagre.username='" + username + "'" : "") +
//                ") AS a " +
//                " LEFT JOIN " + PojoTables.UserInfo + " AS userinfo ON userinfo.username=a.agentUsername and userinfo.delFlag=0 " +
//                (user.getUserType() == UserType.ADMIN ? (" and a.agentusername='" + user.getUsername() + "'") : "") +
//                " LEFT JOIN " + PojoTables.OrderInfo + " AS ori ON ori.agentId=a.userAgentId AND ori.delFlag=0  and ori.createtime BETWEEN " + startTime + " AND " + endTime +
//                " LEFT JOIN " + PojoTables.OrderPay + " AS orp ON orp.orderno=ori.orderno and orp.delFlag=0 " +
//                " LEFT JOIN " + PojoTables.USER_WALLET + " AS uwt ON a.agentUsername=uwt.username " +
//                " GROUP BY a.agentUsername,agentProv,agentCity,agentCounty";
        if (ValidateUtil.isNull(city)) {
            city = "";
        }
        if (ValidateUtil.isNull(county)) {
            county = "";
        }

        String pIdStr = "";

        if ("-1".equals(pIdStr)) {
            pIdStr = "";
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }
//        StringBuffer sb = new StringBuffer();
//        sb.append("CALL city_admin_statistics ('").append(city).append("','").append(county).append("',").append(startTime).append(",").append(endTime).append(")");
//
//        System.out.println(sb.toString());
        int total = 0;
//        long t1 = System.currentTimeMillis();
        List<HashMap<String, Object>> list = null;
        try {
//            list = UserDaoSql.getInstance().queryBySql(sql.toString());
//            list = UserDaoSql.getInstance().queryBySql(sb.toString());
            list = UserDaoSql.getInstance().queryByProcedure("city_admin_statistics_v2", city, county, pIdStr, startTime + "", endTime + "");
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
//        long t2 = System.currentTimeMillis();
//        System.out.println("t2-t1:" + (t2 - t1));
        total = list != null && !list.isEmpty() ? list.size() : 0;
        ShopAndCityVo info = new ShopAndCityVo();
        List<ShopAndCityVo> re = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            HashMap<String, Object> map = list.get(i);
            ShopAndCityVo shopAndCityVo = new ShopAndCityVo();
            shopAndCityVo.setName(map.get("name") == null ? "" : map.get("name").toString()).setUsername(map.get("adminUsername") == null ? "" : map.get("adminUsername").toString());
            shopAndCityVo.setUsername(map.get("username") + "");
            shopAndCityVo.setAllPayMoney(map.get("allPayMoney") == null ? 0 : Double.parseDouble(map.get("allPayMoney").toString()));
            shopAndCityVo.setAllPayPrice(map.get("allPayPrice") == null ? 0 : Double.parseDouble(map.get("allPayPrice").toString()));
            shopAndCityVo.setAllRateMoney(shopAndCityVo.getAllPayPrice() - shopAndCityVo.getAllPayMoney() >= 0 ? shopAndCityVo.getAllPayPrice() - shopAndCityVo.getAllPayMoney() : 0D);
            shopAndCityVo.setOrderCounts(map.get("orderCounts") == null ? 0 : Integer.parseInt(map.get("orderCounts").toString()));
            shopAndCityVo.setRefundApplyCount(map.get("refundApplyCount") == null ? 0 : Integer.parseInt(map.get("refundApplyCount").toString()));
            shopAndCityVo.setRefundApplyMoney(map.get("refundApplyMoney") == null ? 0 : Double.parseDouble(map.get("refundApplyMoney").toString()));
            shopAndCityVo.setCityAgentMoney(map.get("cityAdminMoney") == null ? 0 : Double.parseDouble(map.get("cityAdminMoney").toString()));
            shopAndCityVo.setCityAdminAward(map.get("cityAdminAward") == null ? 0 : Double.parseDouble(map.get("cityAdminAward").toString()));
            shopAndCityVo.setAgentManagerMoney(map.get("agentManagerMoney") == null ? 0 : Double.parseDouble(map.get("agentManagerMoney").toString()));

            shopAndCityVo.setShopAwardMoney(map.get("shopAwardMoney") == null ? 0 : Double.parseDouble(map.get("shopAwardMoney").toString()));
            shopAndCityVo.setPlatformAwardMoney(map.get("platformAwardMoney") == null ? 0 : Double.parseDouble(map.get("platformAwardMoney").toString()));
            shopAndCityVo.setPlatformMoney(map.get("platformMoney") == null ? 0 : Double.parseDouble(map.get("platformMoney").toString()));
            shopAndCityVo.setPostCompanyMoney(map.get("postCompanyMoney") == null ? 0 : Double.parseDouble(map.get("postCompanyMoney").toString()));
            shopAndCityVo.setSquareMoney(map.get("squareMoney") == null ? 0 : Double.parseDouble(map.get("squareMoney").toString()));
            shopAndCityVo.setPostmanMoney(map.get("postmanMoney") == null ? 0 : Double.parseDouble(map.get("postmanMoney").toString()));
            shopAndCityVo.setDeliveryPrice(map.get("deliveryPrice") == null ? 0 : Double.parseDouble(map.get("deliveryPrice").toString()));
            shopAndCityVo.setOriginalYunFeiShop(map.get("originalYunFeiShop") == null ? 0 : Double.parseDouble(map.get("originalYunFeiShop").toString()));
            shopAndCityVo.setOriginalYunFei(map.get("originalYunFei") == null ? 0 : Double.parseDouble(map.get("originalYunFei").toString()));

            shopAndCityVo.setPacketPrice(map.get("packetPrice") == null ? 0 : Double.parseDouble(map.get("packetPrice").toString()));
            shopAndCityVo.setTotalGoodsMoney(map.get("totalGoodsMoney") == null ? 0 : Double.parseDouble(map.get("totalGoodsMoney").toString()));
            shopAndCityVo.setShopMoney(map.get("shopMoney") == null ? 0 : Double.parseDouble(map.get("shopMoney").toString()));

            shopAndCityVo.setMoney(map.get("money") == null ? 0 : Double.parseDouble(map.get("money").toString()));
            shopAndCityVo.setTotalMoney(map.get("totalMoney") == null ? 0 : Double.parseDouble(map.get("totalMoney").toString()));
            shopAndCityVo.setPopTotalMoney(map.get("popTotalMoney") == null ? 0 : Double.parseDouble(map.get("popTotalMoney").toString()));
            shopAndCityVo.setPopMoney(map.get("popMoney") == null ? 0 : Double.parseDouble(map.get("popMoney").toString()));

            shopAndCityVo.setCityAdminMoney1(map.get("cityAdminMoney1") == null ? 0 : Double.parseDouble(map.get("cityAdminMoney1").toString()));
            shopAndCityVo.setCityAdminMoney2(map.get("cityAdminMoney2") == null ? 0 : Double.parseDouble(map.get("cityAdminMoney2").toString()));
            shopAndCityVo.setCityAdminMoney3(map.get("cityAdminMoney3") == null ? 0 : Double.parseDouble(map.get("cityAdminMoney3").toString()));
            shopAndCityVo.setCityAdminMoney4(map.get("cityAdminMoney4") == null ? 0 : Double.parseDouble(map.get("cityAdminMoney4").toString()));
            shopAndCityVo.setCityAdminAward1(map.get("cityAdminAward1") == null ? 0 : Double.parseDouble(map.get("cityAdminAward1").toString()));
            shopAndCityVo.setCityAdminAward2(map.get("cityAdminAward2") == null ? 0 : Double.parseDouble(map.get("cityAdminAward2").toString()));
            shopAndCityVo.setCityAdminAward3(map.get("cityAdminAward3") == null ? 0 : Double.parseDouble(map.get("cityAdminAward3").toString()));
            shopAndCityVo.setCityAdminAward4(map.get("cityAdminAward4") == null ? 0 : Double.parseDouble(map.get("cityAdminAward4").toString()));

            shopAndCityVo.setAgentProv(map.get("agentProv").toString())
                    .setAgentCity(map.get("agentCity").toString())
                    .setAgentCounty(map.get("agentCounty").toString());
            re.add(shopAndCityVo);

            info.setAllPayMoney(info.getAllPayMoney() + shopAndCityVo.getAllPayMoney());
            info.setAllPayPrice(info.getAllPayPrice() + shopAndCityVo.getAllPayPrice());
            info.setOrderCounts(info.getOrderCounts() + shopAndCityVo.getOrderCounts());
            info.setAllRateMoney(info.getAllRateMoney() + (shopAndCityVo.getAllPayPrice() - shopAndCityVo.getAllPayMoney() >= 0 ? shopAndCityVo.getAllPayPrice() - shopAndCityVo.getAllPayMoney() : 0D));
            info.setRefundApplyCount(info.getRefundApplyCount() + shopAndCityVo.getRefundApplyCount());
            info.setRefundApplyMoney(info.getRefundApplyMoney() + shopAndCityVo.getRefundApplyMoney());
            info.setCityAgentMoney(info.getCityAgentMoney() + shopAndCityVo.getCityAgentMoney());
            info.setCityAdminAward(info.getCityAdminAward() + shopAndCityVo.getCityAdminAward());
            info.setAgentManagerMoney(info.getAgentManagerMoney() + shopAndCityVo.getAgentManagerMoney());

            info.setShopMoney(info.getShopMoney() + shopAndCityVo.getShopMoney());
            info.setShopAwardMoney(info.getShopAwardMoney() + shopAndCityVo.getShopAwardMoney());
            info.setPlatformAwardMoney(info.getPlatformAwardMoney() + shopAndCityVo.getPlatformAwardMoney());
            info.setPlatformMoney(info.getPlatformMoney() + shopAndCityVo.getPlatformMoney());
            info.setPostmanMoney(info.getPostmanMoney() + shopAndCityVo.getPostmanMoney());

            info.setSquareMoney(info.getSquareMoney() + shopAndCityVo.getSquareMoney());
            info.setPostCompanyMoney(info.getPostCompanyMoney() + shopAndCityVo.getPostCompanyMoney());

            info.setDeliveryPrice(info.getDeliveryPrice() + shopAndCityVo.getDeliveryPrice());
            info.setOriginalYunFei(info.getOriginalYunFei() + shopAndCityVo.getOriginalYunFei());
            info.setOriginalYunFeiShop(info.getOriginalYunFeiShop() + shopAndCityVo.getOriginalYunFeiShop());

            info.setPacketPrice(info.getPacketPrice() + shopAndCityVo.getPacketPrice());
            info.setTotalGoodsMoney(info.getTotalGoodsMoney() + shopAndCityVo.getTotalGoodsMoney());

            info.setMoney(info.getMoney() + shopAndCityVo.getMoney());
            info.setTotalMoney(info.getTotalMoney() + shopAndCityVo.getTotalMoney());
            info.setPopTotalMoney(info.getPopTotalMoney() + shopAndCityVo.getPopTotalMoney());
            info.setPopMoney(info.getPopMoney() + shopAndCityVo.getPopMoney());

            info.setCityAdminMoney1(info.getCityAdminMoney1() + shopAndCityVo.getCityAdminMoney1());
            info.setCityAdminMoney2(info.getCityAdminMoney2() + shopAndCityVo.getCityAdminMoney2());
            info.setCityAdminMoney3(info.getCityAdminMoney3() + shopAndCityVo.getCityAdminMoney3());
            info.setCityAdminMoney4(info.getCityAdminMoney4() + shopAndCityVo.getCityAdminMoney4());

            info.setCityAdminAward1(info.getCityAdminAward1() + shopAndCityVo.getCityAdminAward1());
            info.setCityAdminAward2(info.getCityAdminAward2() + shopAndCityVo.getCityAdminAward2());
            info.setCityAdminAward3(info.getCityAdminAward3() + shopAndCityVo.getCityAdminAward3());
            info.setCityAdminAward4(info.getCityAdminAward4() + shopAndCityVo.getCityAdminAward4());
        }
        if (null == isExcel) {
            isExcel = 0;
        }
        if (isExcel == 1) {
            // 导出excel
            String downloadUrl = agentMoneyExcel(re);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                if (ValidateUtil.isNotNetUrl(downloadUrl)) {
                    downloadUrl = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                }
                return AjaxResult.successResult(downloadUrl);
            }
        } else {
            re = (List<ShopAndCityVo>) ToolUtils.list(re, (page - 1) * size + 1, page * size);

            re = re == null ? Collections.emptyList() : re;
            StringBuffer buffer = new StringBuffer();
            buffer.append("{\"status\":1,\"size\":").append(re.size())
                    .append(",\"totalCount\":").append(total)
                    .append(",\"data\":").append(JSONArray.toJSON(re))
                    .append(",\"info\":").append(JSONObject.toJSON(info))
                    .append("}");
            return buffer.toString();
        }
        return AjaxResult.successResult(0, 0, null);
    }

    //---------------------------------生成营业统计Excel
    private String agentMoneyExcel(List<ShopAndCityVo> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            NumberFormat nf = new NumberFormat("#0.00");   // 设置数字格式
            WritableCellFormat wcfN = new WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "姓名"));
            sheet.addCell(new Label(col++, row, "帐号"));
            sheet.addCell(new Label(col++, row, "地址"));
            sheet.addCell(new Label(col++, row, "平台收入"));
            sheet.addCell(new Label(col++, row, "商家收入"));
            sheet.addCell(new Label(col++, row, "代理收入"));
            sheet.addCell(new Label(col++, row, "支付金额（不含退款）"));
            sheet.addCell(new Label(col++, row, "商品金额"));
            sheet.addCell(new Label(col++, row, "配送费"));
//            sheet.addCell(new Label(col++, row, "店铺优惠配送费"));
//            sheet.addCell(new Label(col++, row, "代理优惠配送费"));
            sheet.addCell(new Label(col++, row, "打包费"));
            sheet.addCell(new Label(col++, row, "平台优惠"));
            sheet.addCell(new Label(col++, row, "店铺优惠"));
            sheet.addCell(new Label(col++, row, "代理优惠"));
            sheet.addCell(new Label(col++, row, "总单量（不含退款）"));
            sheet.addCell(new Label(col++, row, "总单量（含已退款）"));
            sheet.addCell(new Label(col++, row, "退款订单量"));
            sheet.addCell(new Label(col++, row, "退款金额"));
            sheet.addCell(new Label(col++, row, "正在提现金额"));
            sheet.addCell(new Label(col++, row, "累计提现金额"));
            sheet.addCell(new Label(col++, row, "余额"));
            sheet.addCell(new Label(col++, row, "总金额"));
            row = 1;
            for (ShopAndCityVo record : list) {
                col = 0;
                double totalPrice = 0;

                sheet.addCell(new Label(col++, row, record.getName()));
                sheet.addCell(new Label(col++, row, record.getUsername()));
                sheet.addCell(new Label(col++, row, record.getAgentProv() + " " + record.getAgentCity() + " " + record.getAgentCounty()));
                sheet.addCell(new Number(col++, row, record.getPlatformMoney()));
                sheet.addCell(new Number(col++, row, record.getShopMoney()));
                sheet.addCell(new Number(col++, row, record.getCityAgentMoney() + record.getCityAdminMoney1() + record.getCityAdminMoney2() + record.getCityAdminMoney3() + record.getCityAdminMoney4()));
                sheet.addCell(new Number(col++, row, record.getAllPayMoney())); // 支付金额不含退款
                sheet.addCell(new Number(col++, row, record.getTotalGoodsMoney())); // 商品金额
                sheet.addCell(new Number(col++, row, record.getOriginalYunFei()));// 配送费
//                sheet.addCell(new Number(col++, row, record.getOriginalYunFeiShop()));// 配送费
//                sheet.addCell(new Number(col++, row, record.getOriginalYunFei() - record.getOriginalYunFeiShop() - record.getDeliveryPrice()));// 配送费
                sheet.addCell(new Number(col++, row, record.getPacketPrice()));// 打包费
                sheet.addCell(new Number(col++, row, record.getPlatformAwardMoney()));
                double cityAdminYunFei = record.getOriginalYunFei() - record.getOriginalYunFeiShop() - record.getDeliveryPrice() > 0 ? record.getOriginalYunFei() - record.getOriginalYunFeiShop() - record.getDeliveryPrice() : 0;
                sheet.addCell(new Number(col++, row, record.getShopAwardMoney() + record.getOriginalYunFeiShop()));
                sheet.addCell(new Number(col++, row, record.getCityAdminAward() + record.getCityAdminAward1() + record.getCityAdminAward2() + record.getCityAdminAward3() + record.getCityAdminAward4() + cityAdminYunFei));
                sheet.addCell(new Number(col++, row, record.getOrderCounts()));       // 总单量（不含退款）
                sheet.addCell(new Number(col++, row, record.getOrderCounts() + record.getRefundApplyCount()));       // 总单量（含退款）
                sheet.addCell(new Number(col++, row, record.getRefundApplyCount()));    //退款订单量
                sheet.addCell(new Number(col++, row, record.getRefundApplyMoney()));
                sheet.addCell(new Number(col++, row, record.getPopMoney()));
                sheet.addCell(new Number(col++, row, record.getPopTotalMoney()));
                sheet.addCell(new Number(col++, row, record.getMoney())); // 余额
                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            outputPath = JYFileUtils.saveFile2Oss(outputPath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }


    // 【快点点/产品需求】 管理后台营业统计。开发后部署到奇惠购和快点点里（按照日期显示）
    @RequestMapping(method = RequestMethod.POST, value = "admin/money/city/statistics/byDay")
    @ResponseBody
    public String cityAgentStatis02(String token, String province, String county, String city, Long startTime, Long endTime) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代可查看
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN) {
            JSONObject result = new JSONObject();
            result.put("status", 1);
            result.put("totalCount", 0);
            result.put("data", Collections.EMPTY_LIST);
            return result.toString();
        }
        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
//        if (endTime - startTime >= DateUtils.oneDayMills * 60) {
//            return AjaxResult.errorResult("连续查询不能超过60天");
//        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        /*
        SELECT ori.totalPrice, COUNT(ori.id) orderCounts,COUNT(DISTINCT ori.username) userCounts,
              SUM(CASE WHEN ori.isRefundApply=2 THEN 1 ELSE 0 END) refundApplyCount,
              SUM(CASE WHEN ori.isRefundApply=2  THEN ori.totalprice ELSE 0 END) refundApplyMoney,
              SUM(CASE WHEN ori.paytype ='wx'  THEN IFNULL(ori.totalprice,0) ELSE 0 END) wxPrice,
              SUM(CASE WHEN ori.paytype ='alipay' THEN IFNULL(ori.totalprice,0) ELSE 0 END) aliPrice,
              SUM(CASE WHEN ori.paytype ='wx_xcx' THEN IFNULL(ori.totalprice,0) ELSE 0 END) wx_xcxPrice,
              SUM(CASE WHEN ori.paytype !='wx' AND ori.paytype !='alipay' AND ori.paytype !='wx_xcx' THEN IFNULL(ori.totalprice,0) ELSE 0 END) othPrice,
             SUM(IFNULL(orp.platformMoney,0)) AS 平台收入,
             SUM(IFNULL(orp.shopMoney,0)) AS 商家金额,
             SUM(IFNULL(orp.postmanMoney,0)) AS 配送费,
             SUM(IFNULL(orp.cityAdminMoney,0)) AS 城代收入,
             SUM(IFNULL(orp.shopAwardMoney,0)) AS 店铺补贴,
             SUM(IFNULL(orp.platformAwardMoney,0)) AS 平台补贴,
             FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') AS orderDate
         FROM city_agent AS cag
         LEFT JOIN order_info AS ori ON ori.agentid = cag.id  and ori.delFlag=0 AND ori.orderStatus=9 AND ori.paytype IS NOT NULL AND ori.createTime BETWEEN 1522719130713 AND 1528944636918
         LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno
         WHERE cag.delFlag=0 and cag.province='河北' AND cag.county='大城县' AND ori.createTime IS NOT NULL
         GROUP BY orderDate
         */
        String sql = "SELECT SUM(IFNULL(ori.totalprice,0)) as allPayMoney, COUNT(ori.id) orderCounts,COUNT(DISTINCT ori.username) userCounts, " +
                "       SUM(CASE WHEN ori.isRefundApply=2 THEN 1 ELSE 0 END) refundApplyCount," +
                "       SUM(CASE WHEN ori.isRefundApply=2  THEN ori.totalprice ELSE 0 END) refundApplyMoney," +
                "       SUM(CASE WHEN ori.paytype ='wx' THEN IFNULL(ori.totalprice,0) ELSE 0 END) wxPrice, " +
                "       SUM(CASE WHEN ori.paytype ='alipay' THEN IFNULL(ori.totalprice,0) ELSE 0 END) aliPrice, " +
                "       SUM(CASE WHEN ori.paytype ='wx_xcx' THEN IFNULL(ori.totalprice,0) ELSE 0 END) wx_xcxPrice, " +
                "       SUM(CASE WHEN ori.paytype !='wx' AND ori.paytype !='alipay' AND ori.paytype !='wx_xcx' THEN IFNULL(ori.totalprice,0) ELSE 0 END) othPrice, " +
                "       SUM(IFNULL(orp.platformMoney,0)) AS plateMoney, " +
                "       SUM(IFNULL(orp.shopMoney,0)) AS shopMoney, " +
                "       SUM(IFNULL(ori.deliveryPrice,0)) AS postmanMoney, " +
                "       SUM(IFNULL(orp.cityAdminMoney,0)) AS cityAdminMoney," +
                "       SUM(IFNULL(orp.shopAwardMoney,0)) AS shopAwardMoney," +
                "       SUM(IFNULL(orp.platformAwardMoney,0)) AS platformAwardMoney," +
                "       FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') AS orderDate " +
                " FROM city_agent AS cag  " +
                " LEFT JOIN order_info AS ori ON ori.agentid = cag.id AND ori.delFlag=0 AND ori.orderStatus=9 AND ori.paytype IS NOT NULL AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                " LEFT JOIN order_pay AS orp ON orp.orderno=ori.orderno " +
                " WHERE cag.delFlag=0 AND ori.createTime IS NOT NULL " +
                (StringUtils.isEmpty(province) ? "" : " AND cag.province='" + province + "'") +
                (StringUtils.isEmpty(city) ? "" : " AND cag.city='" + city + "'") +
                (StringUtils.isEmpty(county) ? "" : " AND cag.county='" + county + "'") +
                " GROUP BY orderDate ";
        List<HashMap<String, Object>> list = null;
        long t1 = System.currentTimeMillis();
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        long t2 = System.currentTimeMillis();
        System.out.println("t2-t1:" + (t2 - t1));

        List<TradeStatisByDayInfo> re = null;
        if (list != null && !list.isEmpty()) {
            re = new ArrayList<>();
            for (int i = 0; i < list.size(); i++) {
                HashMap<String, Object> map = list.get(i);
                TradeStatisByDayInfo info = new TradeStatisByDayInfo();
                info.setAllPayMoney(Double.parseDouble(map.get("allPayMoney").toString())).setOrderCounts(Integer.parseInt(map.get("orderCounts").toString()));
                info.setUserCounts(Integer.parseInt(map.get("userCounts").toString())).setRefundApplyCount(Integer.parseInt(map.get("refundApplyCount").toString()));
                info.setRefundApplyMoney(Double.parseDouble(map.get("refundApplyMoney").toString())).setWxPrice(Double.parseDouble(map.get("wxPrice").toString()));
                info.setAliPrice(Double.parseDouble(map.get("aliPrice").toString())).setWx_xcxPrice(Double.parseDouble(map.get("wx_xcxPrice").toString()));
                info.setOthPrice(Double.parseDouble(map.get("othPrice").toString())).setPlateMoney(Double.parseDouble(map.get("plateMoney").toString()));
                info.setShopMoney(Double.parseDouble(map.get("shopMoney").toString())).setPostmanMoney(Double.parseDouble(map.get("postmanMoney").toString()));
                info.setCityAgentMoney(Double.parseDouble(map.get("cityAdminMoney").toString())).setPlatformAwardMoney(Double.parseDouble(map.get("platformAwardMoney").toString()));
                info.setOrderDate(map.get("orderDate").toString());
                re.add(info);
            }
        }

        List actualList = re == null ? Collections.EMPTY_LIST : re;
        JSONObject result = new JSONObject();
        result.put("status", Integer.valueOf(1));
        result.put("size", Integer.valueOf(actualList.size()));
        result.put("data", JSONArray.toJSON(actualList));

        return result.toString();
    }

    // 管理后台，店铺统计
    @RequestMapping(method = RequestMethod.POST, value = "admin/money/shop/statistics")
    @ResponseBody
    public String shopStatis(String token, String province, String county, Integer cloudShopType, String sysAdminUsername, Long agentId, String city, Integer orderBy, Integer orderType, Integer shopRole, Long startTime, Long endTime, Long shopId,
                             Integer page, Integer size, Integer isExcel) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.AGENT_MANAGER && user.getUserType() != UserType.COMPANY && user.getUserType() != UserType.SUPER_COMPANY && user.getUserType() != UserType.SUPER_ADMIN) {
            return AjaxResult.successResult(null, 0);
        }

        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }

        String t_order_info = PojoTables.OrderInfo;
        String t_order_pay = PojoTables.OrderPay;
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {
            String dataDbName = OrderBakTaskUtils.getDataDbName();
            if (ValidateUtil.isNotNull(dataDbName)) {
                t_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
                t_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
            }
        }

//        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
//            if (ValidateUtil.isNotNull(city)) {
//                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
//            }
//        }

//        店铺名称 店铺地址 订单数量 营业额 客单价 店铺补贴 平台补贴 退款订单量 退款订单金额 可提现金额 累计营业额 累计已提现


//         商品金额 支付金额 商家收入 代理收益 代理铺贴
        String sql = "SELECT sfo.id,sfo.cloudShopType,sfo.shopname,sfo.id AS shopId,sfo.ownUsername,sfo.shopRole," +
                "       sfo.province,sfo.city,sfo.county," +
                // "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.totalprice,0) ELSE 0 END) prices,COUNT(ori.orderNo) orderCounts," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.totalprice,0) ELSE 0 END) prices," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " or ori.orderstatus=" + ORDER_STATUS.PAYED_CANCEL + " THEN 1 ELSE 0 END) orderCounts," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.shopAwardMoney,0) ELSE 0 END) shopAwardMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.platformAwardMoney,0) ELSE 0 END) platformAwardMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.platformMoney,0) ELSE 0 END) platformMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.totalGoodsMoney,0) ELSE 0 END) totalGoodsMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.deliveryPrice,0) ELSE 0 END) deliveryPrice," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.originalYunFeiShop,0) ELSE 0 END) AS originalYunFeiShop," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.originalYunFei,0) ELSE 0 END) AS originalYunFei," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.postmanMoney,0) ELSE 0 END) postmanMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.postCompanyMoney,0) ELSE 0 END) postCompanyMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.squareMoney,0) ELSE 0 END) squareMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(ori.packetPrice,0) ELSE 0 END) packetPrice," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.shopMoney,0) ELSE 0 END) shopMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " AND orp.isOrderCancel=0 THEN IFNULL(orp.agentManagerMoney,0) ELSE 0 END) agentManagerMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney,0) ELSE 0 END) cityAdminMoney," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward,0) ELSE 0 END) cityAdminAward," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney1,0) ELSE 0 END) cityAdminMoney1," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney2,0) ELSE 0 END) cityAdminMoney2," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney3,0) ELSE 0 END) cityAdminMoney3," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminMoney4,0) ELSE 0 END) cityAdminMoney4," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward1,0) ELSE 0 END) cityAdminAward1," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward2,0) ELSE 0 END) cityAdminAward2," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward3,0) ELSE 0 END) cityAdminAward3," +
                "       SUM(CASE WHEN ori.orderstatus=" + ORDER_STATUS.OVER + " AND ori.isRefundApply!=" + REFUND_APPLY_STATUS.PASS + " THEN IFNULL(orp.cityAdminAward4,0) ELSE 0 END) cityAdminAward4," +
                "       IFNULL(uwt.money,0) money," +
                "       IFNULL(uwt.totalMoney,0) totalMoney," +
                "       IFNULL(uwt.popTotalMoney,0) popTotalMoney," +
                "       IFNULL(uwt.popMoney,0) popMoney," +
                // "       SUM(CASE WHEN ori.isRefundApply=2 or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN 1 ELSE 0 END) refundApplyCount," +
                // "       SUM(CASE WHEN ori.isRefundApply=2 or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " or ori.orderStatus=" + ORDER_STATUS.PAYED_APPLY_CANCEL + " THEN ori.totalprice ELSE 0 END) refundApplyMoney" +
                "       SUM(CASE WHEN ori.isRefundApply=2 or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " THEN 1 ELSE 0 END) refundApplyCount," +
                "       SUM(CASE WHEN ori.isRefundApply=2 or ori.orderStatus=" + ORDER_STATUS.PAYED_CANCEL + " THEN ori.totalprice ELSE 0 END) refundApplyMoney" +
                " FROM " + PojoTables.ShopInfo + " AS sfo  " +
                " LEFT JOIN " + PojoTables.USER_WALLET + " AS uwt ON uwt.username=sfo.ownUsername  AND uwt.delFlag =0 " +
                " LEFT JOIN " + t_order_info + " AS ori ON ori.shopId=sfo.id AND ori.ordertype!=5 and ori.delFlag=0 " +
                " AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                " LEFT JOIN " + t_order_pay + " AS orp ON orp.orderno=ori.orderno  and orp.delFlag=0 " +
                ((ValidateUtil.isNotNull(sysAdminUsername) || (agentId != null && agentId != 0)) ?
                        " LEFT JOIN " + PojoTables.SHOP_AGENT_RELATION + " AS sar ON sar.shopId=sfo.id and sar.delFlag =0 " +
                                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=sar.agentId and cag.delFlag =0  " +
                                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentId = cag.id and uar.delFlag =0  " : " "
                ) +
                ((user.getUserType() == UserType.AGENT_MANAGER) ?
                        " LEFT JOIN " + PojoTables.SHOP_AGENT_RELATION + " AS sar ON sar.shopId=sfo.id and sar.delFlag =0 " +
                                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=sar.agentId and cag.delFlag =0  " : " "
                ) +
                " WHERE sfo.delFlag=0 and  (sfo.cloudShopType&" + CLOUD_SHOP_TYPE.CLOUD_SHOP_TYPE_SQUARE + ") <=0" +

                (shopRole != null && shopRole > 0 ? (" AND sfo.shopRole=" + shopRole) : "") +
                (ValidateUtil.isNotNull(province) ? (" AND sfo.province='" + province + "'") : "") +
                (ValidateUtil.isNotNull(city) ? (" AND sfo.city='" + city + "'") : "") +
                (ValidateUtil.isNotNull(county) ? (" AND sfo.county='" + county + "'") : "") +
                (shopId != null && shopId != 0 ? (" AND sfo.id='" + shopId + "'") : "") +
                (agentId != null && agentId != 0 ? (" AND cag.id=" + agentId + "") : " ") +
                (ValidateUtil.isNotNull(sysAdminUsername) ? (" AND uar.username = '" + sysAdminUsername + "'") : "") +
                (user.getUserType() == UserType.AGENT_MANAGER ? (" AND cag.agentManagerUsername = '" + user.getUsername() + "'") : "") +
                " GROUP BY sfo.id " +
                " ORDER BY " + (orderBy == null || orderBy == 1 ? "prices " : "") + (orderType == null || orderType == 1 ? " DESC" : " ASC");
        if (null == isExcel) {
            isExcel = 0;
        }
        if (0 == 1 - isExcel) {
        } else {
            sql += " LIMIT " + ((page - 1) * size) + "," + size;
        }


        // 总行数
        String sqlCount = "SELECT COUNT(a.shopId) COUNTS FROM (SELECT sfo.shopname,sfo.id AS shopId,sfo.ownUsername,sfo.shopRole," +
                "       sfo.province,sfo.city,sfo.county," +
                "       SUM(IFNULL(ori.totalprice,0)) prices,COUNT(ori.orderNo) orderCounts" +
                " FROM " + PojoTables.ShopInfo + " AS sfo  " +
                " LEFT JOIN " + PojoTables.USER_WALLET + " AS uwt ON uwt.username=sfo.ownUsername AND uwt.delFlag =0 " +
                " LEFT JOIN " + t_order_info + " AS ori ON ori.shopId=sfo.id and ori.delFlag =0 " +
                " AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                ((ValidateUtil.isNotNull(sysAdminUsername) || (agentId != null && agentId != 0)) ?
                        " LEFT JOIN " + PojoTables.SHOP_AGENT_RELATION + " AS sar ON sar.shopId=sfo.id and sar.delFlag =0 " +
                                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=sar.agentId and cag.delFlag =0  " +
                                " LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " AS uar ON uar.agentId = cag.id and uar.delFlag =0 " : " "
                ) +
                ((user.getUserType() == UserType.AGENT_MANAGER) ?
                        " LEFT JOIN " + PojoTables.SHOP_AGENT_RELATION + " AS sar ON sar.shopId=sfo.id and sar.delFlag =0 " +
                                " LEFT JOIN " + PojoTables.CITY_AGENT + " AS cag ON cag.id=sar.agentId and cag.delFlag =0  " : " "
                ) +
                " WHERE sfo.delFlag=0 " +
                (shopRole != null && shopRole > 0 ? (" AND sfo.shopRole=" + shopRole) : "") +
                (ValidateUtil.isNotNull(province) ? (" AND sfo.province='" + province + "'") : "") +
                (ValidateUtil.isNotNull(city) ? (" AND sfo.city='" + city + "'") : "") +
                (ValidateUtil.isNotNull(county) ? (" AND sfo.county='" + county + "'") : "") +
                (shopId != null && shopId != 0 ? (" AND sfo.id='" + shopId + "'") : "") +
                (null != cloudShopType ? (" AND sfo.cloudShopType=" + cloudShopType + "") : "") +
                (agentId != null && agentId != 0 ? (" AND cag.id=" + agentId + "") : " ") +
                (ValidateUtil.isNotNull(sysAdminUsername) ? (" AND uar.username = '" + sysAdminUsername + "'") : "") +
                (user.getUserType() == UserType.AGENT_MANAGER ? (" AND cag.agentManagerUsername = '" + user.getUsername() + "'") : "") +
//                " AND ori.orderStatus=9 " +
//                " AND ori.createTime BETWEEN " + startTime + " AND " + endTime +
                " GROUP BY sfo.id) a ";
        List<HashMap<String, Object>> list = null;
        long totalCount = 0;
        HashMap<String, Object> map = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);

            // 导出时不需要查询中条数
            if (0 == 1 - isExcel) {
            } else {
                totalCount = UserDaoSql.getInstance().getCount(sqlCount);
            }
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        if (list != null && !list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                // 计算客单价
                int count = Integer.parseInt(list.get(i).get("orderCounts").toString());
                double prices = Double.parseDouble(list.get(i).get("prices").toString());
                list.get(i).put("price", count <= 0 ? 0D : prices / count);
            }
        }


        if (isExcel == 1) {
            // 导出excel
            String downloadUrl = shopStatisExcel(list);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                if (ValidateUtil.isNotNetUrl(downloadUrl)) {
                    downloadUrl = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                }
                return AjaxResult.successResult(downloadUrl);
            }
        } else {
            return AjaxResult.successResult(list, totalCount);
        }
        return AjaxResult.successResult(0, 0, null);

    }

    //---------------------------------生成店铺统计Excel
    private String shopStatisExcel(List<HashMap<String, Object>> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";

            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);

//        workbook = Workbook.createWorkbook(outputFile, Workbook.getWorkbook(templateFile));

            // 生成名为"学生"的工作表，参数0表示这是第一页
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

//        WritableSheet sheet = workbook.getSheet(0);
            NumberFormat nf = new NumberFormat("#0.00");   // 设置数字格式
            WritableCellFormat wcfN = new WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "名称"));
            sheet.addCell(new Label(col++, row, "账号"));
            sheet.addCell(new Label(col++, row, "地址"));
            sheet.addCell(new Label(col++, row, "平台收入"));
            sheet.addCell(new Label(col++, row, "代理收入"));
            sheet.addCell(new Label(col++, row, "店铺收入"));
            sheet.addCell(new Label(col++, row, "支付金额（不含退款）"));
            sheet.addCell(new Label(col++, row, "商品金额"));
            sheet.addCell(new Label(col++, row, "配送费"));
//            sheet.addCell(new Label(col++, row, "店铺优惠配送费"));
//            sheet.addCell(new Label(col++, row, "代理优惠配送费"));
            sheet.addCell(new Label(col++, row, "打包费"));
            sheet.addCell(new Label(col++, row, "平台优惠"));
            sheet.addCell(new Label(col++, row, "店铺优惠"));
            sheet.addCell(new Label(col++, row, "代理优惠"));
            sheet.addCell(new Label(col++, row, "总单量（不含退款）"));
            sheet.addCell(new Label(col++, row, "总单量（含已退款）"));
            sheet.addCell(new Label(col++, row, "退款订单量"));
            sheet.addCell(new Label(col++, row, "退款订单金额"));
            sheet.addCell(new Label(col++, row, "客单价"));
            sheet.addCell(new Label(col++, row, "正在提现金额"));
            sheet.addCell(new Label(col++, row, "累积提现金额"));
            sheet.addCell(new Label(col++, row, "余额"));
            row = 1;
            for (Map<String, Object> record : list) {
                col = 0;
                double totalPrice = 0;
                sheet.addCell(new Label(col++, row, record.get("shopname").toString()));
                sheet.addCell(new Label(col++, row, record.get("ownUsername").toString()));
                sheet.addCell(new Label(col++, row, record.get("province").toString() + record.get("city") + record.get("county")));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("platformMoney").toString())));

                float cityAdminMoney = Float.parseFloat(record.get("cityAdminMoney").toString());
                float cityAdminMoney1 = Float.parseFloat(record.get("cityAdminMoney1").toString());
                float cityAdminMoney2 = Float.parseFloat(record.get("cityAdminMoney2").toString());
                float cityAdminMoney3 = Float.parseFloat(record.get("cityAdminMoney3").toString());
                float cityAdminMoney4 = Float.parseFloat(record.get("cityAdminMoney4").toString());
                sheet.addCell(new Number(col++, row, cityAdminMoney + cityAdminMoney1 + cityAdminMoney2 + cityAdminMoney3 + cityAdminMoney4));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("shopMoney").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("prices").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("totalGoodsMoney").toString())));

                float originalYunFei = Float.parseFloat(record.get("originalYunFei").toString());
                float originalYunFeiShop = Float.parseFloat(record.get("originalYunFeiShop").toString());
                float deliveryPrice = Float.parseFloat(record.get("deliveryPrice").toString());

                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("originalYunFei").toString())));
//                sheet.addCell(new Label(col++, row, record.get("originalYunFeiShop").toString()));
//                sheet.addCell(new Number(col++, row, originalYunFei - originalYunFeiShop - deliveryPrice));

                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("packetPrice").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("platformAwardMoney").toString())));

                float shopAwardMoney = Float.parseFloat(record.get("shopAwardMoney").toString());

                float shopAward = shopAwardMoney + originalYunFeiShop;
                int scale1 = 2;//设置位数
                int roundingMode1 = 4;//表示四舍五入，可以选择其他舍值方式，例如去尾，等等.
                BigDecimal bd1 = new BigDecimal((double) shopAward);
                bd1 = bd1.setScale(scale1, roundingMode1);
//                shopAward   =  bd1.floatValue();
                sheet.addCell(new Number(col++, row, bd1.doubleValue()));
                float cityAdminAward = Float.parseFloat(record.get("cityAdminAward").toString());
                float cityAdminAward1 = Float.parseFloat(record.get("cityAdminAward1").toString());
                float cityAdminAward2 = Float.parseFloat(record.get("cityAdminAward2").toString());
                float cityAdminAward3 = Float.parseFloat(record.get("cityAdminAward3").toString());
                float cityAdminAward4 = Float.parseFloat(record.get("cityAdminAward4").toString());

                float cityAdminYunFei = originalYunFei - originalYunFeiShop - deliveryPrice > 0 ? originalYunFei - originalYunFeiShop - deliveryPrice : 0;

                float cityAward = cityAdminAward + cityAdminAward1 + cityAdminAward2 + cityAdminAward3 + cityAdminAward4 + cityAdminYunFei;
                int scale2 = 2;//设置位数
                int roundingMode2 = 4;//表示四舍五入，可以选择其他舍值方式，例如去尾，等等.
                BigDecimal bd2 = new BigDecimal((double) cityAward);
                bd2 = bd2.setScale(scale2, roundingMode2);
//                cityAward   =  bd2.floatValue();

                sheet.addCell(new Number(col++, row, bd2.doubleValue()));

                int orderCounts = Integer.parseInt(record.get("orderCounts").toString());
                int refundApplyCount = Integer.parseInt(record.get("refundApplyCount").toString());
                sheet.addCell(new Number(col++, row, orderCounts - refundApplyCount));

                sheet.addCell(new Number(col++, row, orderCounts));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("refundApplyCount").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("refundApplyMoney").toString())));

                float price = Float.parseFloat(record.get("price").toString());


                int scale = 2;//设置位数
                int roundingMode = 4;//表示四舍五入，可以选择其他舍值方式，例如去尾，等等.
                BigDecimal bd = new BigDecimal((double) price);
                bd = bd.setScale(scale, roundingMode);
//                price   =  bd.floatValue();
                sheet.addCell(new Number(col++, row, bd.doubleValue()));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("popMoney").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("popTotalMoney").toString())));
                sheet.addCell(new Number(col++, row, Float.parseFloat(record.get("money").toString())));
                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            outputPath = JYFileUtils.saveFile2Oss(outputPath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }

    /**
     * 菜品分析
     *
     * @param token
     * @param province
     * @param county
     * @param city
     * @param shopRole
     * @param startTime
     * @param endTime
     * @param orderBy   1销售额  2销量
     * @param orderType 1-由高到低   2-由低到高
     * @return
     */
    @RequestMapping(method = RequestMethod.POST, value = "admin/money/shop-goods/statistics")
    @ResponseBody
    public String cityAgentGoodsStatis(String token, String province, String county, String city, Integer shopRole, Long startTime, Long endTime, Integer page, Integer size,
                                       Integer orderBy, Integer orderType) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代可查看
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN) {
            return AjaxResult.successResult(Collections.EMPTY_LIST, 0);
        }
        /*
         * SELECT ogs.name,SUM(ogs.totalCount) counst,SUM(ogs.totalprice) prices FROM order_goods AS ogs
            LEFT JOIN order_info AS oir ON oir.orderno=ogs.orderNo
            LEFT JOIN city_agent AS cag ON cag.id=oir.agentId
            LEFT JOIN user_agent_relation AS uagre ON uagre.agentId=cag.id
            WHERE oir.createTime BETWEEN 1522719130713 AND 1528944636918 AND cag.province='河北' AND cag.county='大城县' AND uagre.agentUsername=''
            GROUP BY ogs.name
            ORDER BY counst DESC
         */
        if (startTime == null) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null) {
            endTime = new Date().getTime();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        String sql = "SELECT ogs.name,ogs.goodsId,gof.price,oir.shopId,sfo.shopName,sfo.province,sfo.city,sfo.county,sfo.region," +
                "       SUM(CASE WHEN oir.isRefundApply=2 THEN 1 ELSE 0 END) refundApplyCount," +
                "       SUM(ogs.totalCount) counst,SUM(IFNULL(ogs.totalprice,0)) prices FROM order_goods AS ogs " +
                " LEFT JOIN order_info AS oir ON oir.orderno=ogs.orderNo " +
                " LEFT JOIN city_agent AS cag ON cag.id=oir.agentId " +
                " LEFT JOIN shop_info AS sfo ON sfo.id=oir.shopId AND oir.ordertype!=5" +
//                " LEFT JOIN order_info AS ori ON ori.shopId=sfo.id AND ori.ordertype!=5 " +
                " LEFT JOIN user_agent_relation AS uagre ON uagre.agentId=cag.id " +
                " LEFT JOIN goods_info AS gof ON gof.id=ogs.goodsId " +
                " WHERE oir.orderStatus=9 and oir.createTime BETWEEN " + startTime + " AND " + endTime +
                (StringUtils.isNotEmpty(province) ? (" AND cag.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND cag.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND cag.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? (" AND uagre.agentUsername='" + user.getUsername() + "'") : "") +
                (shopRole != null && shopRole > 0 ? (" AND sfo.shopRole=" + shopRole) : "") +
                " GROUP BY ogs.goodsId " +
                " ORDER BY " + (orderBy == null || orderBy == 1 ? "prices " : "counst ") + (orderType == null || orderType == 1 ? " DESC" : " ASC") +
                " LIMIT " + ((page - 1) * size) + "," + size;
        String sqlCounts = "SELECT COUNT(a.counts) counts from (SELECT COUNT(*) counts FROM order_goods AS ogs" +
                " LEFT JOIN order_info AS oir ON oir.orderno=ogs.orderNo " +
                " LEFT JOIN city_agent AS cag ON cag.id=oir.agentId " +
                " LEFT JOIN shop_info AS sfo ON sfo.id=oir.shopId " +
                " LEFT JOIN user_agent_relation AS uagre ON uagre.agentId=cag.id" +
                " WHERE oir.orderStatus=9 and oir.createTime BETWEEN " + startTime + " AND " + endTime +
                (StringUtils.isNotEmpty(province) ? (" AND cag.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND cag.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND cag.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? (" AND uagre.agentUsername='" + user.getUsername() + "'") : "") +
                (shopRole != null && shopRole > 0 ? (" AND sfo.shopRole=" + shopRole) : "") +
                " GROUP BY ogs.goodsId )a";
        String sqlMoneySum = "SELECT SUM(IFNULL(ogs.totalprice,0)) prices FROM order_goods AS ogs " +
                " LEFT JOIN order_info AS oir ON oir.orderno=ogs.orderNo " +
                " LEFT JOIN city_agent AS cag ON cag.id=oir.agentId " +
                " LEFT JOIN shop_info AS sfo ON sfo.id=oir.shopId " +
                " LEFT JOIN user_agent_relation AS uagre ON uagre.agentId=cag.id" +
                " WHERE oir.orderStatus=9 and oir.createTime BETWEEN " + startTime + " AND " + endTime +
                (StringUtils.isNotEmpty(province) ? (" AND cag.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND cag.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND cag.county='" + county + "'") : "") +
                (user.getUserType() == UserType.ADMIN ? (" AND uagre.agentUsername='" + user.getUsername() + "'") : "") +
                (shopRole != null && shopRole > 0 ? (" AND sfo.shopRole=" + shopRole) : "");

        List<HashMap<String, Object>> list = null;
        long totalCount = 0;
        Double money = 0D;
        HashMap<String, Object> map = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
            totalCount = UserDaoSql.getInstance().getCount(sqlCounts);
            map = UserDaoSql.getInstance().queryMapBySql(sqlMoneySum);
        } catch (SQLException e) {
            return AjaxResult.errorResult(e.getMessage());
        }
        if (map != null && map.containsKey("prices") && map.get("prices") != null) {
            money = Double.parseDouble(map.get("prices").toString());
        }
        // 计算比重
        if (list != null && !list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                list.get(i).put("rate", money == 0D ? 0 : (Double.parseDouble(list.get(i).get("prices").toString())) / money);
                // 获取店铺名称
                Long shopId = Long.parseLong(list.get(i).get("shopId").toString());
                ShopInfo shopInfo = ShopMemory.getInstance().getShopById(shopId);
                list.get(i).put("shopName", shopInfo == null ? "" : shopInfo.getShopName());
            }
        }

        return AjaxResult.successResult(list, totalCount);
    }



    // 将 商品和商品规格整合
    private static void conformGoodsAndGoodsSpecs(Map<Long, List<ShopGoodsSell>> map, List<ShopGoodsSell> goodsSellList) {
        if (map == null || goodsSellList == null || map.isEmpty() || goodsSellList.isEmpty()) {
            return;
        }
        for (ShopGoodsSell sell : goodsSellList) {
            List<ShopGoodsSell> specsList = map.get(sell.getGoodsId());
            if (specsList != null) {
                sell.setGoodsSpecsList(specsList);
                Integer count = 0;
                for (ShopGoodsSell sellA : specsList) {
                    sellA.setGoodsCategoryName(sell.getGoodsCategoryName())
                            .setGoodsSpecsId(sell.getGoodsSpecsId())
                            .setGoodsName(sell.getGoodsName());
                    count += sellA.getSellCount() == null ? 0 : sellA.getSellCount();
                }
                sell.setSellCount(count);
            }
        }
    }
}
